
import geopandas as gpd
import pandas as pd
import numpy as np
from shapely.geometry import Point

from matplotlib import pyplot as plt
import seaborn as sns

import hvplot.pandas
import holoviews as hv


# Show all columns
pd.options.display.max_columns = 999


# Download data on magnitude 2.5+ quakes from the past week
endpoint_url = "http://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_week.geojson"
df = gpd.read_file(endpoint_url)


df.head()


fig, ax = plt.subplots(figsize=(10, 10))

# plot the country outline
world = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
world.plot(ax=ax, facecolor="none", edgecolor="black")

# plot the earthquakes
df.plot(ax=ax, color="crimson", markersize=30, edgecolor='k', linewidth=0.5)

ax.set_axis_off()


# base URL
url = "https://services.arcgis.com/fLeGjb7u4uXqeF9q/arcgis/rest/services/Zipcodes_Poly/FeatureServer/0/"


import esri2gpd


zip_codes = esri2gpd.get(url)


zip_codes.head()


zip_codes.crs

<Geographic 2D CRS: EPSG:4326>
Name: WGS 84
Axis Info [ellipsoidal]:
- Lat[north]: Geodetic latitude (degree)
- Lon[east]: Geodetic longitude (degree)
Area of Use:
- name: World.
- bounds: (-180.0, -90.0, 180.0, 90.0)
Datum: World Geodetic System 1984 ensemble
- Ellipsoid: WGS 84
- Prime Meridian: Greenwich


fig, ax = plt.subplots(figsize=(6, 6))
zip_codes.to_crs(epsg=3857).plot(ax=ax, facecolor="none", edgecolor="black")
ax.set_axis_off()


import requests


# the API end point
API_endpoint = "https://phl.carto.com/api/v2/sql"

# the query
query = "SELECT * FROM shootings" # table name is "shootings"

# desired format of the returned features
output_format = 'geojson'

# fields to skip
skipfields = ["cartodb_id"]


# all of our request parameters
params = dict(q=query, format=output_format, skipfields=skipfields)

params

{'q': 'SELECT * FROM shootings',
 'format': 'geojson',
 'skipfields': ['cartodb_id']}


# make the request
r = requests.get(API_endpoint, params=params)

r

<Response [200]>


# Get the returned data in JSON format
# This is a dictionary
features = r.json()


type(features)

dict


# What are the keys?
list(features.keys())

['type', 'features']


features['type']

'FeatureCollection'


# Let's look at the first feature
features['features'][0]

{'type': 'Feature',
 'geometry': {'type': 'Point', 'coordinates': [-75.211936, 39.967338]},
 'properties': {'objectid': 5448061,
  'year': 2021,
  'dc_key': '202116027102.0',
  'code': '401',
  'date_': '2021-08-07T00:00:00Z',
  'time': '15:12:00',
  'race': 'B',
  'sex': 'M',
  'age': '21',
  'wound': 'Multiple',
  'officer_involved': 'N',
  'offender_injured': 'N',
  'offender_deceased': 'N',
  'location': '800 BLOCK LEX ST',
  'latino': 0,
  'point_x': None,
  'point_y': None,
  'dist': '16',
  'inside': 0,
  'outside': 1,
  'fatal': 0}}


shootings = gpd.GeoDataFrame.from_features(features, crs="EPSG:4326")


shootings.head()


# Load the city limits from open data philly
city_limits = gpd.read_file(
    "https://opendata.arcgis.com/datasets/405ec3da942d4e20869d4e1449a2be48_0.geojson"
).to_crs(epsg=3857)


# make sure we remove missing geometries
shootings = shootings.dropna(subset=['geometry'])

# convert to a better CRS
shootings = shootings.to_crs(epsg=3857)


# Remove any shootings that are outside the city limits
shootings = gpd.sjoin(shootings, city_limits, predicate='within', how='inner').drop(columns=['index_right'])


fig, ax = plt.subplots(figsize=(6, 6))

# ZIP codes
zip_codes.to_crs(epsg=3857).plot(ax=ax, facecolor="none", edgecolor="black")

# Shootings
shootings.plot(ax=ax, color="crimson", markersize=10, alpha=0.4)
ax.set_axis_off()


# initialize the axes
fig, ax = plt.subplots(figsize=(10, 10), facecolor=plt.get_cmap('viridis')(0))

# convert to Web Mercator and plot the hexbins 
x = shootings.geometry.x
y = shootings.geometry.y
ax.hexbin(x, y, gridsize=40, mincnt=1, cmap='viridis')

# overlay the ZIP codes
zip_codes.to_crs(epsg=3857).plot(ax=ax, 
                                 facecolor='none', 
                                 linewidth=0.5,
                                 edgecolor='white')

ax.set_axis_off()


query = "SELECT COUNT(*) FROM shootings"


params = dict(q=query)
r = requests.get(API_endpoint, params=params)


r.json()

{'rows': [{'count': 13255}],
 'time': 0.007,
 'fields': {'count': {'type': 'number', 'pgtype': 'int8'}},
 'total_rows': 1}


# select the first 5
query = "SELECT * FROM shootings LIMIT 5"


params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)


df = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
df


shootings.head()


query = "SELECT * FROM shootings WHERE fatal = 0"


# Make the request
params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)

# Make the GeoDataFrame
fatal = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
print("number of nonfatal shootings = ", len(fatal))

number of nonfatal shootings =  10443


query = "SELECT * FROM shootings WHERE date_ > '12/31/21'"


# Make the request
params = dict(q=query, format="geojson")
r = requests.get(API_endpoint, params=params)

# Make the GeoDataFrame
this_year = gpd.GeoDataFrame.from_features(r.json(), crs="EPSG:4326")
print("number of shootings this year = ", len(this_year))

number of shootings this year =  1836


import carto2gpd


where = "date_ > '12/31/21' and fatal = 0"
df = carto2gpd.get(API_endpoint, 'shootings', where=where)


df.head()


# Limit results to the first 5
df = carto2gpd.get(API_endpoint, 'shootings', limit=5)


len(df)

5

df


size = carto2gpd.get_size(API_endpoint, 'shootings')
print(size)

13255


# Convert the data column to a datetime object
shootings['date'] = pd.to_datetime(shootings['date_'])


# Add new columns: Month and Day of Week
shootings["Month"] = shootings["date"].dt.month
shootings["Day of Week"] = shootings["date"].dt.dayofweek # Monday is 0, Sunday is 6


count = shootings.groupby(['Month', 'Day of Week']).size()
count = count.reset_index(name='Count')
count.head()


# Remember 0 is Monday and 6 is Sunday
count.hvplot.heatmap(
    x="Day of Week",
    y="Month",
    C="Count",
    cmap="viridis",
    width=400,
    height=500,
    flip_yaxis=True,
)


# First step: import cenpy
import cenpy


# UNCOMMENT TO SEE ALL ROWS/COLUMNS IN DATAFRAMES
pd.options.display.max_rows = 9999 
pd.options.display.max_colwidth = 200


available = cenpy.explorer.available()

available.head()


# Return a dataframe of all datasets that start with "ACS"
# Axis=0 means to filter the index labels!
acs = available.filter(regex="^ACS", axis=0)

acs


# Return a dataframe of all datasets that start with "ACSDT5Y"
available.filter(regex="^ACSDT5Y", axis=0)


cenpy.explorer.explain("ACSDT5Y2020")

{'American Community Survey: 5-Year Estimates: Detailed Tables 5-Year': 'The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population. Summary files include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts (117th Congress), all counties, all places, and all tracts and block groups. Summary files contain the most detailed cross-tabulations, many of which are published down to block groups. The data are population and housing counts. There are over 64,000 variables in this dataset.'}


acs = cenpy.remote.APIConnection("ACSDT5Y2020")


len(acs.variables)

27892


acs.variables.head(n=10)


acs.varslike?

Signature: acs.varslike(pattern=None, by=None, engine='re', within=None)
Docstring:
Grabs columns that match a particular search pattern.

Parameters
----------
pattern : str
          a search pattern to match
by      : str
          a column in the APIConnection.variables to conduct the search
          within
engine  : {'re', 'fnmatch', callable}
          backend string matching module to use, or a function of the form
          match(candidate, pattern). (default: 're')
within  : pandas.DataFrame 
          the variables over which to search.

Notes
------
Only regex and fnmatch will be supported modules. Note that, while
regex is the default, the python regular expressions module has some
strange behavior if you're used to VIM or Perl-like regex. It may be
easier to use fnmatch if regex is not providing the results you expect.

If you want, you can also pass an engine that is a function. If so, this
needs to be a function that has a signature like:

fn(candidate, pattern)

and return True or False if the candidate matches the pattern. So, for
instance, you can use any string processing function:

    >>> cxn.varslike('_100M', engine = lambda c,p: c.endswith(p)

which may also be expressed as a regexp:

    >>> cxn.varslike('_100M$', engine='re')

or an fnmatch pattern:

    >>> cxn.varslike('*_100M', engine='fnmatch')
File:      ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py
Type:      method


race_matches =acs.varslike("HISPANIC OR LATINO ORIGIN BY RACE", by='concept').sort_index() # searches along concept column


race_matches


variables = [
    "NAME",
    "B03002_001E", # Total
    "B03002_003E", # Not Hispanic, White
    "B03002_004E", # Not Hispanic, Black
    "B03002_005E", # Not Hispanic, American Indian
    "B03002_006E", # Not Hispanic, Asian
    "B03002_007E", # Not Hispanic, Native Hawaiian
    "B03002_008E", # Not Hispanic, Other
    "B03002_009E", # Not Hispanic, Two or More Races
    "B03002_012E", # Hispanic
]


acs.geographies['fips']


counties = cenpy.explorer.fips_table("COUNTY")
counties.head()


# Trim to just Philadelphia
# Search for rows where name contains "Philadelphia"
counties.loc[ counties[3].str.contains("Philadelphia") ]


philly_county_code = "101"
pa_state_code = "42"


philly_demo_data = acs.query(
    cols=variables,
    geo_unit="block group:*",
    geo_filter={"state": pa_state_code, 
                "county": philly_county_code, 
                "tract": "*"},
)


philly_demo_data.head()


len(philly_demo_data)

1338


for variable in variables:
    
    # Convert all variables EXCEPT for NAME
    if variable != "NAME":
        philly_demo_data[variable] = philly_demo_data[variable].astype(float)


acs.query(
    cols=variables,
    geo_unit="block group:*",
    geo_filter={"state": pa_state_code},
)

/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:224: SyntaxWarning: "is not" with a literal. Did you mean "!="?
  if index is not "":
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:224: SyntaxWarning: "is not" with a literal. Did you mean "!="?
  if index is not "":

---------------------------------------------------------------------------
JSONDecodeError                           Traceback (most recent call last)
File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/requests/models.py:971, in Response.json(self, **kwargs)
    970 try:
--> 971     return complexjson.loads(self.text, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/__init__.py:525, in loads(s, encoding, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, use_decimal, **kw)
    521 if (cls is None and encoding is None and object_hook is None and
    522         parse_int is None and parse_float is None and
    523         parse_constant is None and object_pairs_hook is None
    524         and not use_decimal and not kw):
--> 525     return _default_decoder.decode(s)
    526 if cls is None:

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/decoder.py:370, in JSONDecoder.decode(self, s, _w, _PY3)
    369     s = str(s, self.encoding)
--> 370 obj, end = self.raw_decode(s)
    371 end = _w(s, end).end()

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/simplejson/decoder.py:400, in JSONDecoder.raw_decode(self, s, idx, _w, _PY3)
    399         idx += 3
--> 400 return self.scan_once(s, idx=_w(s, idx).end())

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

JSONDecodeError                           Traceback (most recent call last)
File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:219, in APIConnection.query(self, cols, geo_unit, geo_filter, apikey, **kwargs)
    218 try:
--> 219     json_content = res.json()
    220     df = pd.DataFrame().from_records(json_content[1:], columns=json_content[0])

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/requests/models.py:975, in Response.json(self, **kwargs)
    972 except JSONDecodeError as e:
    973     # Catch JSON-related errors and raise as requests.JSONDecodeError
    974     # This aliases json.JSONDecodeError and simplejson.JSONDecodeError
--> 975     raise RequestsJSONDecodeError(e.msg, e.doc, e.pos)

JSONDecodeError: Expecting value: line 1 column 1 (char 0)

During handling of the above exception, another exception occurred:

HTTPError                                 Traceback (most recent call last)
Input In [70], in <cell line: 1>()
----> 1 acs.query(
      2     cols=variables,
      3     geo_unit="block group:*",
      4     geo_filter={"state": pa_state_code},
      5 )

File ~/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/cenpy/remote.py:229, in APIConnection.query(self, cols, geo_unit, geo_filter, apikey, **kwargs)
    227 except (ValueError, JSONDecodeError):
    228     if res.status_code == 400:
--> 229         raise r.HTTPError(
    230             "400 " + "\n".join(map(lambda x: x.decode(), res.iter_lines()))
    231         )
    232     else:
    233         res.raise_for_status()

HTTPError: 400 error: unknown/unsupported geography heirarchy


cenpy.tiger.available?


cenpy.tiger.available()

[{'name': 'AIANNHA', 'type': 'MapServer'},
 {'name': 'CBSA', 'type': 'MapServer'},
 {'name': 'Hydro', 'type': 'MapServer'},
 {'name': 'Labels', 'type': 'MapServer'},
 {'name': 'Legislative', 'type': 'MapServer'},
 {'name': 'Places_CouSub_ConCity_SubMCD', 'type': 'MapServer'},
 {'name': 'PUMA_TAD_TAZ_UGA_ZCTA', 'type': 'MapServer'},
 {'name': 'Region_Division', 'type': 'MapServer'},
 {'name': 'School', 'type': 'MapServer'},
 {'name': 'Special_Land_Use_Areas', 'type': 'MapServer'},
 {'name': 'State_County', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2012', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2013', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2014', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2015', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2016', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2017', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2018', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2019', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2021', 'type': 'MapServer'},
 {'name': 'tigerWMS_ACS2022', 'type': 'MapServer'},
 {'name': 'tigerWMS_Census2010', 'type': 'MapServer'},
 {'name': 'tigerWMS_Census2020', 'type': 'MapServer'},
 {'name': 'tigerWMS_Current', 'type': 'MapServer'},
 {'name': 'tigerWMS_ECON2012', 'type': 'MapServer'},
 {'name': 'tigerWMS_PhysicalFeatures', 'type': 'MapServer'},
 {'name': 'Tracts_Blocks', 'type': 'MapServer'},
 {'name': 'Transportation', 'type': 'MapServer'},
 {'name': 'TribalTracts', 'type': 'MapServer'},
 {'name': 'Urban', 'type': 'MapServer'},
 {'name': 'USLandmass', 'type': 'MapServer'}]


acs.set_mapservice("tigerWMS_Census2020")

Connection to American Community Survey: 5-Year Estimates: Detailed Tables 5-Year(ID: https://api.census.gov/data/id/ACSDT5Y2020)
With MapServer: Census 2020 WMS


acs.mapservice.layers

[(ESRILayer) Urban Growth Areas,
 (ESRILayer) Urban Growth Areas Labels,
 (ESRILayer) Tribal Census Tracts,
 (ESRILayer) Tribal Census Tracts Labels,
 (ESRILayer) Tribal Block Groups,
 (ESRILayer) Tribal Block Groups Labels,
 (ESRILayer) Census Tracts,
 (ESRILayer) Census Tracts Labels,
 (ESRILayer) Census Block Groups,
 (ESRILayer) Census Block Groups Labels,
 (ESRILayer) Census Blocks,
 (ESRILayer) Census Blocks Labels,
 (ESRILayer) Unified School Districts,
 (ESRILayer) Unified School Districts Labels,
 (ESRILayer) Secondary School Districts,
 (ESRILayer) Secondary School Districts Labels,
 (ESRILayer) Elementary School Districts,
 (ESRILayer) Elementary School Districts Labels,
 (ESRILayer) Estates,
 (ESRILayer) Estates Labels,
 (ESRILayer) County Subdivisions,
 (ESRILayer) County Subdivisions Labels,
 (ESRILayer) Subbarrios,
 (ESRILayer) Subbarrios Labels,
 (ESRILayer) Consolidated Cities,
 (ESRILayer) Consolidated Cities Labels,
 (ESRILayer) Incorporated Places,
 (ESRILayer) Incorporated Places Labels,
 (ESRILayer) Census Designated Places,
 (ESRILayer) Census Designated Places Labels,
 (ESRILayer) Alaska Native Regional Corporations,
 (ESRILayer) Alaska Native Regional Corporations Labels,
 (ESRILayer) Tribal Subdivisions,
 (ESRILayer) Tribal Subdivisions Labels,
 (ESRILayer) Federal American Indian Reservations,
 (ESRILayer) Federal American Indian Reservations Labels,
 (ESRILayer) Off-Reservation Trust Lands,
 (ESRILayer) Off-Reservation Trust Lands Labels,
 (ESRILayer) State American Indian Reservations,
 (ESRILayer) State American Indian Reservations Labels,
 (ESRILayer) Hawaiian Home Lands,
 (ESRILayer) Hawaiian Home Lands Labels,
 (ESRILayer) Alaska Native Village Statistical Areas,
 (ESRILayer) Alaska Native Village Statistical Areas Labels,
 (ESRILayer) Oklahoma Tribal Statistical Areas,
 (ESRILayer) Oklahoma Tribal Statistical Areas Labels,
 (ESRILayer) State Designated Tribal Statistical Areas,
 (ESRILayer) State Designated Tribal Statistical Areas Labels,
 (ESRILayer) Tribal Designated Statistical Areas,
 (ESRILayer) Tribal Designated Statistical Areas Labels,
 (ESRILayer) American Indian Joint-Use Areas,
 (ESRILayer) American Indian Joint-Use Areas Labels,
 (ESRILayer) 116th Congressional Districts,
 (ESRILayer) 116th Congressional Districts Labels,
 (ESRILayer) 2018 State Legislative Districts - Upper,
 (ESRILayer) 2018 State Legislative Districts - Upper Labels,
 (ESRILayer) 2018 State Legislative Districts - Lower,
 (ESRILayer) 2018 State Legislative Districts - Lower Labels,
 (ESRILayer) Voting Districts,
 (ESRILayer) Voting Districts Labels,
 (ESRILayer) Census Divisions,
 (ESRILayer) Census Divisions Labels,
 (ESRILayer) Census Regions,
 (ESRILayer) Census Regions Labels,
 (ESRILayer) Combined New England City and Town Areas,
 (ESRILayer) Combined New England City and Town Areas Labels,
 (ESRILayer) New England City and Town Area Divisions,
 (ESRILayer) New England City and Town Area  Divisions Labels,
 (ESRILayer) Metropolitan New England City and Town Areas,
 (ESRILayer) Metropolitan New England City and Town Areas Labels,
 (ESRILayer) Micropolitan New England City and Town Areas,
 (ESRILayer) Micropolitan New England City and Town Areas Labels,
 (ESRILayer) Combined Statistical Areas,
 (ESRILayer) Combined Statistical Areas Labels,
 (ESRILayer) Metropolitan Divisions,
 (ESRILayer) Metropolitan Divisions Labels,
 (ESRILayer) Metropolitan Statistical Areas,
 (ESRILayer) Metropolitan Statistical Areas Labels,
 (ESRILayer) Micropolitan Statistical Areas,
 (ESRILayer) Micropolitan Statistical Areas Labels,
 (ESRILayer) States,
 (ESRILayer) States Labels,
 (ESRILayer) Counties,
 (ESRILayer) Counties Labels,
 (ESRILayer) Zip Code Tabulation Areas,
 (ESRILayer) Zip Code Tabulation Areas Labels,
 (ESRILayer) Public Use Microdata Areas,
 (ESRILayer) Public Use Microdata Areas Labels]


block_group_geoservice = acs.mapservice.layers[8]

block_group_geoservice

(ESRILayer) Census Block Groups


block_group_geoservice.variables


# The base url for the map service API endpoint
block_group_geoservice._baseurl

'http://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/tigerWMS_Census2020/MapServer/8'


## We're just querying a GeoService — let's use esri2gpd

# Only Philadelphia
where_clause = "STATE = '42' AND COUNTY = '101'"

# Query
philly_block_groups = esri2gpd.get(block_group_geoservice._baseurl, where=where_clause)


len(philly_block_groups)

1338


philly_block_groups.head()


philly_demo_final = philly_block_groups.merge(
    philly_demo_data,
    left_on=["STATE", "COUNTY", "TRACT", "BLKGRP"],
    right_on=["state", "county", "tract", "block group"],
)


philly_demo_final.head()


# Check the CRS
philly_demo_final.crs

<Geographic 2D CRS: EPSG:4326>
Name: WGS 84
Axis Info [ellipsoidal]:
- Lat[north]: Geodetic latitude (degree)
- Lon[east]: Geodetic longitude (degree)
Area of Use:
- name: World.
- bounds: (-180.0, -90.0, 180.0, 90.0)
Datum: World Geodetic System 1984 ensemble
- Ellipsoid: WGS 84
- Prime Meridian: Greenwich


fig, ax = plt.subplots(figsize=(10,10))

# Plot the choropleth
philly_demo_final.plot(ax=ax, column='B03002_001E', legend=True)

# Format
ax.set_title("Population of Philadelphia by Block Group", fontsize=16)
ax.set_axis_off()


cols = ['NAME_x', 'B03002_001E', 'geometry']
philly_demo_final[cols].hvplot(c='B03002_001E',
                              geo=True, 
                              legend=True,
                              width=600, 
                              height=400, 
                              cmap='viridis',
                              frame_height=800, 
                              frame_width=800, 
                              hover_cols=["NAME_x"])


# Rename columns
philly_demo_final = philly_demo_final.rename(
    columns={
        "B03002_001E": "Total",  # Total
        "B03002_003E": "White",  # Not Hispanic, White
        "B03002_004E": "Black",  # Not Hispanic, Black
        "B03002_005E": "AI/AN",  # Not Hispanic, American Indian
        "B03002_006E": "Asian",  # Not Hispanic, Asian
        "B03002_007E": "NH/PI",  # Not Hispanic, Native Hawaiian
        "B03002_008E": "Other_",  # Not Hispanic, Other
        "B03002_009E": "Two Plus",  # Not Hispanic, Two or More Races
        "B03002_012E": "Hispanic",  # Hispanic
    }
)


# Add an "Other" column 
cols = ['AI/AN', 'NH/PI','Other_', 'Two Plus']
philly_demo_final['Other'] = philly_demo_final[cols].sum(axis=1)


def random_points_in_polygon(number, polygon):
    """
    Generate a random number of points within the 
    specified polygon.
    """
    points = []
    min_x, min_y, max_x, max_y = polygon.bounds
    i= 0
    while i < number:
        point = Point(np.random.uniform(min_x, max_x), np.random.uniform(min_y, max_y))
        if polygon.contains(point):
            points.append(point)
            i += 1
    return points


# get the first block group polygon in the data set
geo = philly_demo_final.iloc[0].geometry

geo


fig, ax = plt.subplots(figsize=(6, 6))

# Generate some random points
random_points = random_points_in_polygon(100, geo)

# Plot random points
gpd.GeoSeries(random_points).plot(ax=ax, markersize=20, color="red")

# Plot boundary of block group
gpd.GeoSeries([geo]).plot(ax=ax, facecolor="none", edgecolor="black")

ax.set_axis_off()


def generate_dot_map(data, people_per_dot):
    """
    Given a GeoDataFrame with demographic columns, generate a dot 
    map according to the population in each geometry.
    """
    results = []
    for field in ["White", "Hispanic", "Black", "Asian", "Other"]:

        # generate random points
        pts = data.apply(
            lambda row: random_points_in_polygon(
                row[field] / people_per_dot, row["geometry"]
            ),
            axis=1,
        )

        # combine into single GeoSeries
        pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
        pts.name = "geometry"

        # make into a GeoDataFrame
        pts = gpd.GeoDataFrame(pts)
        pts["field"] = field

        # save
        results.append(pts)

    return gpd.GeoDataFrame(pd.concat(results), crs=data["geometry"].crs).reset_index(
        drop=True
    )


dot_map = generate_dot_map(philly_demo_final, people_per_dot=50)

/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_72938/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_72938/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_72938/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_72938/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/Users/nhand/mambaforge/envs/musa-550-fall-2022/lib/python3.9/site-packages/pandas/core/dtypes/cast.py:122: ShapelyDeprecationWarning: The array interface is deprecated and will no longer work in Shapely 2.0. Convert the '.coords' to a numpy array instead.
  arr = construct_1d_object_array_from_listlike(values)
/var/folders/49/ntrr94q12xd4rq8hqdnx96gm0000gn/T/ipykernel_72938/2645225740.py:18: FutureWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.
  pts = gpd.GeoSeries(pts.apply(pd.Series).stack(), dtype=object, crs=data["geometry"].crs)


print("number of points = ", len(dot_map))

number of points =  34143


dot_map.tail()


# setup a custom color map from ColorBrewer
from matplotlib.colors import ListedColormap

cmap = ListedColormap(
    ["#3a833c", "#377eb8", "#4daf4a", "#984ea3", "#ff7f00", "#ffff33"]
)


# Convert to 3857
dot_map_3857 = dot_map.to_crs(epsg=3857)


# Initialize the figure and axes
fig, ax = plt.subplots(figsize=(10, 10), facecolor="#cfcfcf")

# Plot
dot_map_3857.plot(
    ax=ax,
    column="field",
    categorical=True,
    legend=True,
    alpha=1,
    markersize=0.5,
    cmap=cmap,
)

# format
ax.set_title("Philadelphia, PA", fontsize=16)
ax.text(
    0.5, 0.95, "1 dot = 50 people", fontsize=12, transform=ax.transAxes, ha="center"
)
ax.set_axis_off()


philly_demo_tract = acs.query(
    cols=["NAME", "B03002_001E", "B03002_004E"],
    geo_unit="tract:*",
    geo_filter={
                "state" : "42", 
                "county" : "101"
               },
)


philly_demo_tract.head()


philly_demo_tract.dtypes # "object" means string!

NAME           object
B03002_001E    object
B03002_004E    object
state          object
county         object
tract          object
dtype: object


# Census tracts are the 7th layer (index 6 starting from 0)
acs.mapservice.layers[6]

(ESRILayer) Census Tracts


# The base url for the map service API endpoint
url = acs.mapservice.layers[6]._baseurl

url

'http://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/tigerWMS_Census2020/MapServer/6'


## We're just querying a GeoService — let's use esri2gpd

# Only Philadelphia
where_clause = "STATE = '42' AND COUNTY = '101'"

# Query
philly_census_tracts = esri2gpd.get(url, where=where_clause)


# Merge them together
# IMPORTANT: Make sure your merge keys are the same dtypes (e.g., all strings or all ints)
philly_demo_tract = philly_census_tracts.merge(
    philly_demo_tract,
    left_on=["STATE", "COUNTY", "TRACT"],
    right_on=["state", "county", "tract"],
)


for col in ['B03002_001E', 'B03002_004E']:
    philly_demo_tract[col] = philly_demo_tract[col].astype(float)


philly_demo_tract["percent_black"] = (
    100 * philly_demo_tract["B03002_004E"] / philly_demo_tract["B03002_001E"]
)


# Documentation includes an example for help!
# carto2gpd.get?


table_name = 'child_blood_lead_levels_by_ct'
lead_levels = carto2gpd.get("https://phl.carto.com/api/v2/sql", table_name)


lead_levels.head()


lead_levels = lead_levels.dropna(subset=['perc_5plus'])


# Trim the lead levels data
lead_levels_trimmed = lead_levels[['census_tract', 'perc_5plus']]

# Merge into the demographic data
# Use "GEOID" — that is the unique identifier here
merged = philly_demo_tract.merge(lead_levels_trimmed, 
                                 how='left', 
                                 left_on='GEOID', 
                                 right_on='census_tract')


merged.head()


merged = merged[['NAME_x', 'geometry', 'percent_black', 'perc_5plus']]


# Lead levels plot
img1 = merged.hvplot(geo=True, 
                     c='perc_5plus', 
                     width=500, 
                     height=400, 
                     cmap='viridis', 
                     title='Lead Levels')

# Percent black 
img2 = merged.hvplot(geo=True,
                     c='percent_black', 
                     width=500, 
                     height=400, 
                     cmap='viridis', 
                     title='% Black')

img1 + img2


cols = ["perc_5plus", "percent_black"]
merged[cols].hvplot.scatter(x=cols[0], y=cols[1])


fig, ax = plt.subplots(figsize=(8,6))

X = merged.dropna()
sns.kdeplot(x=X['perc_5plus'], y=X['percent_black'], ax=ax);

	id	mag	place	time	updated	tz	url	detail	felt	cdi	mmi	alert	status	sig	net	code	ids	sources	types	nst	dmin	rms	gap	magType	type	title	geometry
0	us6000ir62	4.4	Kepulauan Barat Daya, Indonesia	1664996940115	1665002070040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	298	us	6000ir62	,us6000ir62,	,us,	,origin,phase-data,	17.0	2.435	1.1200	134.00	mb	earthquake	M 4.4 - Kepulauan Barat Daya, Indonesia	POINT Z (128.89230 -7.48840 134.26800)
1	us6000ir61	4.8	44 km SE of Ōfunato, Japan	1664996651760	1665004355392	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	1.0	4.1	NaN	None	reviewed	355	us	6000ir61	,us6000ir61,	,us,	,dyfi,origin,phase-data,	61.0	2.158	0.6100	96.00	mb	earthquake	M 4.8 - 44 km SE of Ōfunato, Japan	POINT Z (142.04170 38.76410 64.08000)
2	us6000ir5p	5.1	South Sandwich Islands region	1664993809163	1664994838040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	400	us	6000ir5p	,us6000ir5p,	,us,	,origin,phase-data,	50.0	7.695	0.7100	85.00	mb	earthquake	M 5.1 - South Sandwich Islands region	POINT Z (-25.10380 -58.69850 10.00000)
3	usd000i6b2	5.3	South Sandwich Islands region	1664986638676	1664988681040	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	432	us	d000i6b2	,usd000i6b2,	,us,	,origin,phase-data,	77.0	7.640	0.3500	84.00	mb	earthquake	M 5.3 - South Sandwich Islands region	POINT Z (-25.20070 -58.67540 31.80300)
4	nn00848597	2.5	19 km SSE of Alamo, Nevada	1664964881656	1664985075494	None	https://earthquake.usgs.gov/earthquakes/eventp...	https://earthquake.usgs.gov/earthquakes/feed/v...	NaN	NaN	NaN	None	reviewed	96	nn	00848597	,nn00848597,	,nn,	,origin,phase-data,	36.0	0.205	0.1913	89.78	ml	earthquake	M 2.5 - 19 km SSE of Alamo, Nevada	POINT Z (-115.08070 37.20280 9.80000)

	geometry	OBJECTID	CODE	COD	Shape__Area	Shape__Length
0	POLYGON ((-75.11107 40.04682, -75.11206 40.047...	1	19120	20	9.177970e+07	49921.544063
1	POLYGON ((-75.19227 39.99463, -75.19240 39.994...	2	19121	21	6.959879e+07	39534.887217
2	POLYGON ((-75.15406 39.98601, -75.15494 39.986...	3	19122	22	3.591632e+07	24124.645221
3	POLYGON ((-75.15190 39.97056, -75.15258 39.970...	4	19123	23	3.585175e+07	26421.728982
4	POLYGON ((-75.09660 40.04249, -75.09661 40.042...	5	19124	24	1.448080e+08	63658.770420

	Month	Day of Week	Count
0	1	0	135
1	1	1	129
2	1	2	120
3	1	3	125
4	1	4	131

	c_isTimeseries	c_isMicrodata	publisher	temporal	spatial	programCode	modified	keyword	contactPoint	distribution	description	bureauCode	accessLevel	title	c_isAvailable	c_isCube	c_isAggregate	c_dataset	vintage
ABSCB2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-04-30 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2017.0
ABSCB2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-10-26 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:Erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2018.0
ABSCB2019	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2021-08-17 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:ERD.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2019/abscb', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscb,)	2019.0
ABSCBO2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-04-30 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/abscbo', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscbo,)	2017.0
ABSCBO2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:007	2020-10-26 00:00:00.0	(census,)	{'fn': 'ASE Staff', 'hasEmail': 'mailto:erd.annual.survey.of.entrepreneurs@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/abscbo', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Annual Business Survey (ABS) provides information on selected economic and demographic characteristics for businesses and business owners by sex, ethnicity, race, and veteran status. Further, ...	006:07	public	Economic Surveys: Annual Business Survey: Annual Business Survey	True	NaN	True	(abscbo,)	2018.0

	c_isTimeseries	c_isMicrodata	publisher	temporal	spatial	programCode	modified	keyword	contactPoint	distribution	description	bureauCode	accessLevel	title	c_isAvailable	c_isCube	c_isAggregate	c_dataset	vintage
ACSDT5Y2009	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-08-27 13:11:18.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2009/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2009.0
ACSDT5Y2010	NaN	NaN	U.S. Census Bureau	NaN	United States	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2010.0
ACSDT5Y2011	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2011/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2011.0
ACSDT5Y2012	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2012/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2012.0
ACSDT5Y2013	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2013/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2013.0
ACSDT5Y2014	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-04 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2014/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2014.0
ACSDT5Y2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-05 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2015.0
ACSDT5Y2016	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-07-05 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2016/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2016.0
ACSDT5Y2017	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2018-08-21 07:11:43.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2017/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	ACS 5-Year Detailed Tables	True	True	True	(acs, acs5)	2017.0
ACSDT5Y2018	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-22 16:28:02.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:Acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2018/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2018.0
ACSDT5Y2019	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-04-03 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:Acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2019/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2019.0
ACSDT5Y2020	NaN	NaN	U.S. Census Bureau	2020/2020	US	006:004	2021-07-13 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2020/acs/acs5', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a br...	006:07	public	American Community Survey: 5-Year Estimates: Detailed Tables 5-Year	True	True	True	(acs, acs5)	2020.0
ACSDT5YAIAN2010	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-24 07:18:57.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5/aian', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Indian and Alaska Native (AIAN) tables are released every five years. They are available for selected tribal populations, with more detailed tribal categories compared to those in the...	006:07	public	American Community Survey: 5-Year Estimates: American Indian and Alaska Native Detailed Tables 5-Year	True	True	True	(acs, acs5, aian)	2010.0
ACSDT5YAIAN2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-02-13 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5/aian', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The American Indian and Alaska Native (AIAN) tables are released every five years. They are available for selected tribal populations, with more detailed tribal categories compared to those in the...	006:07	public	ACS 5-Year AIAN Detailed Tables	True	True	True	(acs, acs5, aian)	2015.0
ACSDT5YSPT2010	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2019-10-11 14:16:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2010/acs/acs5/spt', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Selected Population Tables (SPT) are released every five years. They are available for selected race, Hispanic origin, tribal, and ancestry populations.	006:07	public	American Community Survey: 5-Year Estimates: Selected Population Detailed Tables 5-Year	True	True	True	(acs, acs5, spt)	2010.0
ACSDT5YSPT2015	NaN	NaN	U.S. Census Bureau	NaN	NaN	006:004	2020-02-18 00:00:00.0	(census,)	{'fn': 'American Community Survey Office', 'hasEmail': 'mailto:acso.users.support@census.gov'}	{'@type': 'dcat:Distribution', 'accessURL': 'http://api.census.gov/data/2015/acs/acs5/spt', 'description': 'API endpoint', 'format': 'API', 'mediaType': 'application/json', 'title': 'API endpoint'}	The Selected Population Tables (SPT) are released every five years. They are available for selected race, Hispanic origin, tribal, and ancestry populations.	006:07	public	American Community Survey: 5-Year Estimates: Selected Population Detailed Tables 5-Year	True	True	True	(acs, acs5, spt)	2015.0

	geometry	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	point_x	point_y	dist	outside
0	POINT (-75.21194 39.96734)	5448061	2021	202116027102.0	401	2021-08-07T00:00:00Z	15:12:00	B	M	21	Multiple	N	N	N	800 BLOCK LEX ST	NaN	NaN	16	1.0
1	POINT (-75.22470 39.96608)	5448062	2021	202116027321.0	401	2021-08-09T00:00:00Z	18:29:00	B	M	24	Multiple	N	N	N	300 BLOCK N 52ND ST	NaN	NaN	16	1.0
2	POINT (-75.19151 39.95754)	5448063	2021	202116027351.0	401	2021-08-10T00:00:00Z	02:55:00	B	M	46	Head	N	N	N	3400 BLOCK LANCASTER AVE	NaN	NaN	16	1.0
3	POINT (-75.22010 39.96373)	5448064	2021	202116027401.0	3412	2021-07-17T00:00:00Z	20:02:00	B	M	26	Arm	N	N	N	200 BLOCK N 50TH ST	NaN	NaN	16	1.0
4	POINT (-75.19982 39.96835)	5448065	2021	202116028828.0	3113	2021-08-18T00:00:00Z	13:21:00	B	M	24	Chest	N	N	N	3800 BLOCK RENO ST	NaN	NaN	16	1.0

	geometry	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	point_x	point_y	dist	outside	OBJECTID	Shape__Area	Shape__Length
0	POINT (-8372554.417 4861197.061)	5448061	2021	202116027102.0	401	2021-08-07T00:00:00Z	15:12:00	B	M	21	Multiple	N	N	N	800 BLOCK LEX ST	NaN	NaN	16	1.0	1	3.970706e+09	394751.12047
1	POINT (-8373975.522 4861014.196)	5448062	2021	202116027321.0	401	2021-08-09T00:00:00Z	18:29:00	B	M	24	Multiple	N	N	N	300 BLOCK N 52ND ST	NaN	NaN	16	1.0	1	3.970706e+09	394751.12047
2	POINT (-8370280.160 4859774.025)	5448063	2021	202116027351.0	401	2021-08-10T00:00:00Z	02:55:00	B	M	46	Head	N	N	N	3400 BLOCK LANCASTER AVE	NaN	NaN	16	1.0	1	3.970706e+09	394751.12047
3	POINT (-8373463.229 4860673.747)	5448064	2021	202116027401.0	3412	2021-07-17T00:00:00Z	20:02:00	B	M	26	Arm	N	N	N	200 BLOCK N 50TH ST	NaN	NaN	16	1.0	1	3.970706e+09	394751.12047
4	POINT (-8371206.004 4861343.472)	5448065	2021	202116028828.0	3113	2021-08-18T00:00:00Z	13:21:00	B	M	24	Chest	N	N	N	3800 BLOCK RENO ST	NaN	NaN	16	1.0	1	3.970706e+09	394751.12047

	geometry	cartodb_id	objectid	year	dc_key	code	date_	time	race	sex	age	wound	officer_involved	offender_injured	offender_deceased	location	latino	point_x	point_y	dist	outside
0	POINT (-75.12757 39.99667)	1749	5450802	2022	202224009967.0	401	2022-02-18T00:00:00Z	22:47:00	W	F	36	Back	N	N	N	200 BLOCK E CLEARFIELD ST	1	None	None	24	1
1	POINT (-75.17737 39.99524)	1750	5450803	2022	202222054220.0	3306	2022-09-26T00:00:00Z	20:52:00	B	M	27	Multiple/Head	N	N	N	2800 BLOCK W HUNTINGDON ST	0	None	None	22	1
2	POINT (-75.11953 39.99282)	1751	5450804	2022	202224010283.0	401	2022-02-20T00:00:00Z	13:12:00	B	M	49	Multiple	N	N	N	1800 BLOCK E MONMOUTH ST	0	None	None	24	1
3	POINT (-75.11472 39.99212)	1752	5450805	2022	202224010383.0	401	2022-02-21T00:00:00Z	00:00:00	W	M	35	Multiple/Head	N	N	N	2000 BLOCK E ELKHART ST	1	None	None	24	1
4	POINT (-75.16048 39.97166)	1753	5450806	2022	202222048509.0	411	2022-08-29T00:00:00Z	03:56:00	B	M	37	Multiple	N	N	N	1400 BLOCK W GIRARD AVE	0	None	None	22	1

	label	concept	predicateType	group	predicateOnly	hasGeoCollectionSupport	attributes	required
for	Census API FIPS 'for' clause	Census API Geography Specification	fips-for	N/A	True	NaN	NaN	NaN
in	Census API FIPS 'in' clause	Census API Geography Specification	fips-in	N/A	True	NaN	NaN	NaN
ucgid	Uniform Census Geography Identifier clause	Census API Geography Specification	ucgid	N/A	True	True	NaN	NaN
B24022_060E	Estimate!!Total:!!Female:!!Service occupations:!!Food preparation and serving related occupations	SEX BY OCCUPATION AND MEDIAN EARNINGS IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) FOR THE FULL-TIME, YEAR-ROUND CIVILIAN EMPLOYED POPULATION 16 YEARS AND OVER	int	B24022	NaN	NaN	B24022_060EA,B24022_060M,B24022_060MA	NaN
B19001B_014E	Estimate!!Total:!!$100,000 to $124,999	HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (BLACK OR AFRICAN AMERICAN ALONE HOUSEHOLDER)	int	B19001B	NaN	NaN	B19001B_014EA,B19001B_014M,B19001B_014MA	NaN
B07007PR_019E	Estimate!!Total:!!Moved from different municipio:!!Foreign born:!!Naturalized U.S. citizen	GEOGRAPHICAL MOBILITY IN THE PAST YEAR BY CITIZENSHIP STATUS FOR CURRENT RESIDENCE IN PUERTO RICO	int	B07007PR	NaN	NaN	B07007PR_019EA,B07007PR_019M,B07007PR_019MA	NaN
B19101A_004E	Estimate!!Total:!!$15,000 to $19,999	FAMILY INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (WHITE ALONE HOUSEHOLDER)	int	B19101A	NaN	NaN	B19101A_004EA,B19101A_004M,B19101A_004MA	NaN
B24022_061E	Estimate!!Total:!!Female:!!Service occupations:!!Building and grounds cleaning and maintenance occupations	SEX BY OCCUPATION AND MEDIAN EARNINGS IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) FOR THE FULL-TIME, YEAR-ROUND CIVILIAN EMPLOYED POPULATION 16 YEARS AND OVER	int	B24022	NaN	NaN	B24022_061EA,B24022_061M,B24022_061MA	NaN
B19001B_013E	Estimate!!Total:!!$75,000 to $99,999	HOUSEHOLD INCOME IN THE PAST 12 MONTHS (IN 2020 INFLATION-ADJUSTED DOLLARS) (BLACK OR AFRICAN AMERICAN ALONE HOUSEHOLDER)	int	B19001B	NaN	NaN	B19001B_013EA,B19001B_013M,B19001B_013MA	NaN
B07007PR_018E	Estimate!!Total:!!Moved from different municipio:!!Foreign born:	GEOGRAPHICAL MOBILITY IN THE PAST YEAR BY CITIZENSHIP STATUS FOR CURRENT RESIDENCE IN PUERTO RICO	int	B07007PR	NaN	NaN	B07007PR_018EA,B07007PR_018M,B07007PR_018MA	NaN

	label	concept	predicateType	group	predicateOnly	hasGeoCollectionSupport	attributes	required
B03002_001E	Estimate!!Total:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_001EA,B03002_001M,B03002_001MA	NaN
B03002_002E	Estimate!!Total:!!Not Hispanic or Latino:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_002EA,B03002_002M,B03002_002MA	NaN
B03002_003E	Estimate!!Total:!!Not Hispanic or Latino:!!White alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_003EA,B03002_003M,B03002_003MA	NaN
B03002_004E	Estimate!!Total:!!Not Hispanic or Latino:!!Black or African American alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_004EA,B03002_004M,B03002_004MA	NaN
B03002_005E	Estimate!!Total:!!Not Hispanic or Latino:!!American Indian and Alaska Native alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_005EA,B03002_005M,B03002_005MA	NaN
B03002_006E	Estimate!!Total:!!Not Hispanic or Latino:!!Asian alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_006EA,B03002_006M,B03002_006MA	NaN
B03002_007E	Estimate!!Total:!!Not Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_007EA,B03002_007M,B03002_007MA	NaN
B03002_008E	Estimate!!Total:!!Not Hispanic or Latino:!!Some other race alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_008EA,B03002_008M,B03002_008MA	NaN
B03002_009E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_009EA,B03002_009M,B03002_009MA	NaN
B03002_010E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races including Some other race	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_010EA,B03002_010M,B03002_010MA	NaN
B03002_011E	Estimate!!Total:!!Not Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_011EA,B03002_011M,B03002_011MA	NaN
B03002_012E	Estimate!!Total:!!Hispanic or Latino:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_012EA,B03002_012M,B03002_012MA	NaN
B03002_013E	Estimate!!Total:!!Hispanic or Latino:!!White alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_013EA,B03002_013M,B03002_013MA	NaN
B03002_014E	Estimate!!Total:!!Hispanic or Latino:!!Black or African American alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_014EA,B03002_014M,B03002_014MA	NaN
B03002_015E	Estimate!!Total:!!Hispanic or Latino:!!American Indian and Alaska Native alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_015EA,B03002_015M,B03002_015MA	NaN
B03002_016E	Estimate!!Total:!!Hispanic or Latino:!!Asian alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_016EA,B03002_016M,B03002_016MA	NaN
B03002_017E	Estimate!!Total:!!Hispanic or Latino:!!Native Hawaiian and Other Pacific Islander alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_017EA,B03002_017M,B03002_017MA	NaN
B03002_018E	Estimate!!Total:!!Hispanic or Latino:!!Some other race alone	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_018EA,B03002_018M,B03002_018MA	NaN
B03002_019E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_019EA,B03002_019M,B03002_019MA	NaN
B03002_020E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races including Some other race	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_020EA,B03002_020M,B03002_020MA	NaN
B03002_021E	Estimate!!Total:!!Hispanic or Latino:!!Two or more races:!!Two races excluding Some other race, and three or more races	HISPANIC OR LATINO ORIGIN BY RACE	int	B03002	NaN	NaN	B03002_021EA,B03002_021M,B03002_021MA	NaN
GEO_ID	Geography	SEX BY EDUCATIONAL ATTAINMENT FOR THE POPULATION 25 YEARS AND OVER (SOME OTHER RACE ALONE);SEX BY EDUCATIONAL ATTAINMENT FOR THE POPULATION 25 YEARS AND OVER (TWO OR MORE RACES);SEX BY EDUCATIONAL...	string	B17015,B18104,B17016,B18105,B17017,B18106,B17018,B18107,B19301A,B07001PR,B17011,B17012,B18101,B17013,B18102,B17014,B18103,B19301E,B19301D,B19301C,B19301B,B17010,B19301I,B98013,B99102,B19301H,B9801...	NaN	NaN	NAME	NaN

	name	geoLevelDisplay	referenceDate	requires	wildcard	optionalWithWCFor
0	us	010	2020-01-01	NaN	NaN	NaN
1	region	020	2020-01-01	NaN	NaN	NaN
2	division	030	2020-01-01	NaN	NaN	NaN
3	state	040	2020-01-01	NaN	NaN	NaN
4	county	050	2020-01-01	[state]	[state]	state
5	county subdivision	060	2020-01-01	[state, county]	[county]	county
6	subminor civil division	067	2020-01-01	[state, county, county subdivision]	NaN	NaN
7	place/remainder (or part)	070	2020-01-01	[state, county, county subdivision]	NaN	NaN
8	tract	140	2020-01-01	[state, county]	[county]	county
9	block group	150	2020-01-01	[state, county, tract]	[county, tract]	tract
10	county (or part)	155	2020-01-01	[state, place]	NaN	NaN
11	place	160	2020-01-01	[state]	[state]	state
12	consolidated city	170	2020-01-01	[state]	NaN	NaN
13	place (or part)	172	2020-01-01	[state, consolidated city]	NaN	NaN
14	alaska native regional corporation	230	2020-01-01	[state]	[state]	state
15	american indian area/alaska native area/hawaiian home land	250	2020-01-01	NaN	NaN	NaN
16	tribal subdivision/remainder	251	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
17	american indian area/alaska native area (reservation or statistical entity only)	252	2020-01-01	NaN	NaN	NaN
18	american indian area (off-reservation trust land only)/hawaiian home land	254	2020-01-01	NaN	NaN	NaN
19	tribal census tract	256	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
20	tribal block group	258	2020-01-01	[american indian area/alaska native area/hawaiian home land, tribal census tract]	NaN	NaN
21	state (or part)	260	2020-01-01	[american indian area/alaska native area/hawaiian home land]	NaN	NaN
22	place/remainder (or part)	269	2020-01-01	[american indian area/alaska native area/hawaiian home land, state (or part)]	NaN	NaN
23	county (or part)	270	2020-01-01	[american indian area/alaska native area/hawaiian home land, state (or part)]	NaN	NaN
24	american indian area/alaska native area/hawaiian home land (or part)	280	2020-01-01	[state]	NaN	NaN
25	american indian area/alaska native area (reservation or statistical entity only) (or part)	283	2020-01-01	[state]	NaN	NaN
26	american indian area (off-reservation trust land only)/hawaiian home land (or part)	286	2020-01-01	[state]	NaN	NaN
27	state (or part)	290	2020-01-01	[american indian area/alaska native area/hawaiian home land, tribal subdivision/remainder]	NaN	NaN
28	tribal census tract (or part)	291	2020-01-01	[american indian area (reservation only)]	NaN	NaN
29	tribal census tract (or part)	292	2020-01-01	[american indian area (off-reservation trust land only)/hawaiian home land]	NaN	NaN
30	tribal block group (or part)	293	2020-01-01	[american indian area (reservation only), tribal census tract (or part)]	NaN	NaN
31	tribal block group (or part)	294	2020-01-01	[american indian area (off-reservation trust land only)/hawaiian home land, tribal census tract (or part)]	NaN	NaN
32	metropolitan statistical area/micropolitan statistical area	310	2020-01-01	NaN	NaN	NaN
33	state (or part)	311	2020-01-01	[metropolitan statistical area/micropolitan statistical area]	NaN	NaN
34	principal city (or part)	312	2020-01-01	[metropolitan statistical area/micropolitan statistical area, state (or part)]	NaN	NaN
35	county	313	2020-01-01	[metropolitan statistical area/micropolitan statistical area, state (or part)]	NaN	NaN
36	metropolitan division	314	2020-01-01	[metropolitan statistical area/micropolitan statistical area]	NaN	NaN
37	state (or part)	315	2020-01-01	[metropolitan statistical area/micropolitan statistical area, metropolitan division]	NaN	NaN
38	county	316	2020-01-01	[metropolitan statistical area/micropolitan statistical area, metropolitan division, state (or part)]	NaN	NaN
39	metropolitan statistical area/micropolitan statistical area (or part)	320	2020-01-01	[state]	NaN	NaN
40	principal city (or part)	321	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
41	county	322	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
42	metropolitan division (or part)	323	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part)]	NaN	NaN
43	county	324	2020-01-01	[state, metropolitan statistical area/micropolitan statistical area (or part), metropolitan division (or part)]	NaN	NaN
44	combined statistical area	330	2020-01-01	NaN	NaN	NaN
45	state (or part)	331	2020-01-01	[combined statistical area]	NaN	NaN
46	metropolitan statistical area/micropolitan statistical area	332	2020-01-01	[combined statistical area]	NaN	NaN
47	state (or part)	333	2020-01-01	[combined statistical area, metropolitan statistical area/micropolitan statistical area]	NaN	NaN
48	combined new england city and town area	335	2020-01-01	NaN	NaN	NaN
49	state (or part)	336	2020-01-01	[combined new england city and town area]	[combined new england city and town area]	combined new england city and town area
50	new england city and town area	337	2020-01-01	[combined new england city and town area]	NaN	NaN
51	state (or part)	338	2020-01-01	[combined new england city and town area, new england city and town area]	NaN	NaN
52	combined statistical area (or part)	340	2020-01-01	[state]	NaN	NaN
53	metropolitan statistical area/micropolitan statistical area (or part)	341	2020-01-01	[state, combined statistical area (or part)]	NaN	NaN
54	combined new england city and town area (or part)	345	2020-01-01	[state]	NaN	NaN
55	new england city and town area (or part)	346	2020-01-01	[state, combined new england city and town area (or part)]	NaN	NaN
56	new england city and town area	350	2020-01-01	NaN	NaN	NaN
57	state (or part)	351	2020-01-01	[new england city and town area]	NaN	NaN
58	principal city	352	2020-01-01	[new england city and town area, state (or part)]	NaN	NaN
59	county (or part)	353	2020-01-01	[new england city and town area, state (or part)]	NaN	NaN
60	county subdivision	354	2020-01-01	[new england city and town area, state (or part), county (or part)]	NaN	NaN
61	necta division	355	2020-01-01	[new england city and town area]	NaN	NaN
62	state (or part)	356	2020-01-01	[new england city and town area, necta division]	NaN	NaN
63	county (or part)	357	2020-01-01	[new england city and town area, necta division, state (or part)]	NaN	NaN
64	county subdivision	358	2020-01-01	[new england city and town area, necta division, state (or part), county (or part)]	NaN	NaN
65	new england city and town area (or part)	360	2020-01-01	[state]	NaN	NaN
66	principal city	361	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
67	county (or part)	362	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
68	county subdivision	363	2020-01-01	[state, new england city and town area (or part), county (or part)]	NaN	NaN
69	necta division (or part)	364	2020-01-01	[state, new england city and town area (or part)]	NaN	NaN
70	county (or part)	365	2020-01-01	[state, new england city and town area (or part), necta division (or part)]	NaN	NaN
71	county subdivision	366	2020-01-01	[state, new england city and town area (or part), necta division (or part), county (or part)]	NaN	NaN
72	urban area	400	2020-01-01	NaN	NaN	NaN
73	state	410	2020-01-01	[urban area]	NaN	NaN
74	county	430	2020-01-01	[urban area, state]	NaN	NaN
75	congressional district	500	2020-01-01	[state]	[state]	state
76	county (or part)	510	2020-01-01	[state, congressional district]	NaN	NaN
77	american indian area/alaska native area/hawaiian home land (or part)	550	2020-01-01	[state, congressional district]	NaN	NaN
78	state legislative district (upper chamber)	610	2020-01-01	[state]	NaN	NaN
79	county (or part)	612	2020-01-01	[state, state legislative district (upper chamber)]	NaN	NaN
80	state legislative district (lower chamber)	620	2020-01-01	[state]	NaN	NaN
81	county (or part)	622	2020-01-01	[state, state legislative district (lower chamber)]	NaN	NaN
82	public use microdata area	795	2020-01-01	[state]	[state]	state
83	zip code tabulation area	860	2020-01-01	NaN	NaN	NaN
84	school district (elementary)	950	2020-01-01	[state]	[state]	state
85	school district (secondary)	960	2020-01-01	[state]	[state]	state
86	school district (unified)	970	2020-01-01	[state]	[state]	state

	0	1	2	3	4
0	AL	1	1	Autauga County	H1
1	AL	1	3	Baldwin County	H1
2	AL	1	5	Barbour County	H1
3	AL	1	7	Bibb County	H1
4	AL	1	9	Blount County	H1

	NAME	B03002_001E	B03002_003E	B03002_004E	B03002_006E	B03002_009E	B03002_012E	state	county	tract	block group
0	Block Group 3, Census Tract 1.01, Philadelphia County, Pennsylvania	0	0	0	0	0	0	42	101	000101	3
1	Block Group 1, Census Tract 1.02, Philadelphia County, Pennsylvania	1499	1318	30	40	33	78	42	101	000102	1
2	Block Group 4, Census Tract 1.02, Philadelphia County, Pennsylvania	316	316	0	0	0	0	42	101	000102	4
3	Block Group 2, Census Tract 2, Philadelphia County, Pennsylvania	1090	490	40	548	12	0	42	101	000200	2
4	Block Group 2, Census Tract 5, Philadelphia County, Pennsylvania	0	0	0	0	0	0	42	101	000500	2

	name	type	alias	domain	length
0	OBJECTID	esriFieldTypeOID	OBJECTID	None	NaN
1	AREALAND	esriFieldTypeDouble	AREALAND	None	NaN
2	ALANDHIST	esriFieldTypeDouble	ALANDHIST	None	NaN
3	AREAWATER	esriFieldTypeDouble	AREAWATER	None	NaN
4	AWATERHIST	esriFieldTypeDouble	AWATERHIST	None	NaN
5	BLKGRP	esriFieldTypeString	BLKGRP	None	1.0
6	COUNTY	esriFieldTypeString	COUNTY	None	3.0
7	EFFDATE	esriFieldTypeDate	EFFDATE	None	8.0
8	ESTABDATE	esriFieldTypeDate	ESTABDATE	None	8.0
9	FUNCSTAT	esriFieldTypeString	FUNCSTAT	None	1.0
10	GEOID	esriFieldTypeString	GEOID	None	12.0
11	LSADC	esriFieldTypeString	LSADC	None	2.0
12	MTFCC	esriFieldTypeString	MTFCC	None	5.0
13	BASENAME	esriFieldTypeString	BASENAME	None	100.0
14	NAME	esriFieldTypeString	NAME	None	100.0
15	OID	esriFieldTypeString	OID	None	22.0
16	STATE	esriFieldTypeString	STATE	None	2.0
17	TRACT	esriFieldTypeString	TRACT	None	6.0
18	UR	esriFieldTypeString	UR	None	1.0
19	VINTAGE	esriFieldTypeString	VINTAGE	None	2.0
20	CENTLON	esriFieldTypeString	CENTLON	None	12.0
21	CENTLAT	esriFieldTypeString	CENTLAT	None	11.0
22	INTPTLON	esriFieldTypeString	INTPTLON	None	12.0
23	INTPTLAT	esriFieldTypeString	INTPTLAT	None	11.0
24	HU100	esriFieldTypeDouble	HU100	None	NaN
25	POP100	esriFieldTypeDouble	POP100	None	NaN
26	STGEOMETRY	esriFieldTypeGeometry	STGEOMETRY	None	NaN
27	STGEOMETRY.AREA	esriFieldTypeDouble	STGEOMETRY.AREA	None	NaN
28	STGEOMETRY.LEN	esriFieldTypeDouble	STGEOMETRY.LEN	None	NaN

	geometry	OBJECTID	AREALAND	ALANDHIST	BLKGRP	COUNTY	EFFDATE	ESTABDATE	FUNCSTAT	GEOID	LSADC	MTFCC	BASENAME	NAME	OID	STATE	TRACT	UR	VINTAGE	CENTLON	CENTLAT	INTPTLON	INTPTLAT	HU100	POP100	STGEOMETRY.AREA	STGEOMETRY.LEN
0	POLYGON ((-75.05449 40.02465, -75.05405 40.02413, -75.05383 40.02388, -75.05360 40.02362, -75.05337 40.02336, -75.05315 40.02310, -75.05293 40.02286, -75.05270 40.02259, -75.05248 40.02235, -75.05...	82	305092	305092	3	101	NaN	NaN	S	421010323003	BG	G5030	3	Block Group 3	208703717019554	42	032300	None	70	-075.0499467	+40.0222676	-075.0499467	+40.0222676	744	1869	520848.611219	3194.678561
1	POLYGON ((-75.15987 39.94454, -75.15998 39.94401, -75.15841 39.94381, -75.15762 39.94372, -75.15684 39.94361, -75.15676 39.94396, -75.15673 39.94412, -75.15664 39.94451, -75.15654 39.94499, -75.15...	83	70896	70896	1	101	NaN	NaN	S	421010011021	BG	G5030	1	Block Group 1	208703717019574	42	001102	None	70	-075.1581610	+39.9449699	-075.1581610	+39.9449699	1180	1729	120760.631816	1390.110306
2	POLYGON ((-75.00180 40.10493, -75.00165 40.10480, -75.00150 40.10468, -75.00137 40.10456, -75.00134 40.10447, -74.99980 40.10324, -74.99901 40.10258, -74.99873 40.10231, -74.99838 40.10185, -74.99...	904	423561	417638	2	101	NaN	NaN	S	421010361002	BG	G5030	2	Block Group 2	20870508933460	42	036100	None	70	-074.9965242	+40.1037503	-074.9965242	+40.1037503	404	1134	724822.503578	3558.455582
3	POLYGON ((-75.15255 40.00318, -75.15293 40.00159, -75.15163 40.00143, -75.15071 40.00131, -75.14992 40.00121, -75.14915 40.00110, -75.14874 40.00105, -75.14815 40.00099, -75.14847 40.00177, -75.14...	920	192284	192284	2	101	NaN	NaN	S	421010200002	BG	G5030	2	Block Group 2	208703717019432	42	020000	None	70	-075.1507409	+40.0042065	-075.1507409	+40.0042065	497	747	328094.605123	3138.906384
4	POLYGON ((-75.15216 40.01254, -75.15189 40.01199, -75.15143 40.01090, -75.15107 40.01004, -75.15103 40.00990, -75.15100 40.00982, -75.15081 40.00920, -75.15046 40.00806, -75.15029 40.00750, -75.14...	921	212571	212571	1	101	NaN	NaN	S	421010203001	BG	G5030	1	Block Group 1	208703717019433	42	020300	None	70	-075.1492793	+40.0091183	-075.1492793	+40.0091183	704	1354	362759.782648	3386.729282

	geometry	field
34138	POINT (-75.07918 40.01679)	Other
34139	POINT (-75.24422 39.95975)	Other
34140	POINT (-75.08308 40.02967)	Other
34141	POINT (-75.05612 40.01648)	Other
34142	POINT (-75.21897 39.92928)	Other

	geometry	cartodb_id	census_tract	data_redacted	num_bll_5plus	num_screen	perc_5plus
0	POLYGON ((-75.14147 39.95171, -75.14150 39.95139, -75.14175 39.95000, -75.14177 39.94989, -75.14177 39.94985, -75.14194 39.94869, -75.14205 39.94813, -75.14247 39.94818, -75.14328 39.94828, -75.14...	1	42101000100	False	0.0	100.0	0.0
1	POLYGON ((-75.16238 39.95766, -75.16236 39.95773, -75.16234 39.95784, -75.16175 39.95776, -75.16159 39.95774, -75.16128 39.95770, -75.16079 39.95764, -75.16028 39.95757, -75.15882 39.95739, -75.15...	2	42101000200	True	NaN	109.0	NaN
2	POLYGON ((-75.17821 39.95981, -75.17743 39.95971, -75.17714 39.95967, -75.17549 39.95946, -75.17430 39.95931, -75.17389 39.95925, -75.17325 39.95918, -75.17268 39.95911, -75.17219 39.95904, -75.17...	3	42101000300	True	NaN	110.0	NaN
3	POLYGON ((-75.17299 39.95464, -75.17301 39.95456, -75.17307 39.95422, -75.17318 39.95358, -75.17329 39.95294, -75.17337 39.95262, -75.17337 39.95257, -75.17349 39.95206, -75.17417 39.95215, -75.17...	4	42101000401	True	NaN	61.0	NaN
4	POLYGON ((-75.16333 39.95334, -75.16340 39.95335, -75.16366 39.95338, -75.16390 39.95346, -75.16507 39.95362, -75.16523 39.95259, -75.16530 39.95229, -75.16525 39.95201, -75.16518 39.95189, -75.16...	5	42101000402	False	0.0	41.0	0.0

	geometry	OBJECTID	AREALAND	ALANDHIST	AREAWATER	AWATERHIST	COUNTY	EFFDATE	ESTABDATE	FUNCSTAT	GEOID	LSADC	MTFCC	BASENAME	NAME_x	OID	STATE	TRACT	UR	VINTAGE	CENTLON	CENTLAT	INTPTLON	INTPTLAT	HU100	POP100	STGEOMETRY.AREA	STGEOMETRY.LEN	NAME_y	B03002_001E	B03002_004E	state	county	tract	percent_black	census_tract	perc_5plus
0	POLYGON ((-75.02371 40.13005, -75.02489 40.12922, -75.02319 40.12822, -75.02204 40.12753, -75.02193 40.12747, -75.02051 40.12661, -75.02046 40.12659, -75.02043 40.12657, -75.02010 40.12636, -75.01...	669	1816210	1816210	7169	7169	101	NaN	NaN	S	42101036501	CT	G5020	365.01	Census Tract 365.01	207703717001577	42	036501	None	70	-075.0131963	+40.1293202	-075.0122761	+40.1288937	2520	5698	3.122595e+06	7152.056313	Census Tract 365.01, Philadelphia County, Pennsylvania	4799.0	538.0	42	101	036501	11.210669	NaN	NaN
1	POLYGON ((-75.18329 39.94951, -75.18275 39.94937, -75.18258 39.94928, -75.18221 39.94922, -75.18113 39.94901, -75.18002 39.94887, -75.17899 39.94875, -75.17784 39.94861, -75.17776 39.94897, -75.17...	673	99957	99957	12755	12755	101	NaN	NaN	S	42101000801	CT	G5020	8.01	Census Tract 8.01	207703717001596	42	000801	None	70	-075.1800509	+39.9500600	-075.1804054	+39.9497374	1195	1748	1.920198e+05	1800.716073	Census Tract 8.01, Philadelphia County, Pennsylvania	1631.0	26.0	42	101	000801	1.594114	NaN	NaN
2	POLYGON ((-75.16899 40.07147, -75.16892 40.07143, -75.16673 40.07017, -75.16535 40.06937, -75.16474 40.06903, -75.16449 40.06888, -75.16411 40.06866, -75.16342 40.06933, -75.16274 40.07000, -75.16...	2651	406340	406054	0	0	101	NaN	NaN	S	42101026301	CT	G5020	263.01	Census Tract 263.01	20770510238026	42	026301	None	70	-075.1637161	+40.0728148	-075.1637161	+40.0728148	1656	3726	6.947209e+05	3399.391181	Census Tract 263.01, Philadelphia County, Pennsylvania	3772.0	3435.0	42	101	026301	91.065748	42101026301	3.6
3	POLYGON ((-75.16912 40.02386, -75.16848 40.02351, -75.16765 40.02306, -75.16680 40.02259, -75.16626 40.02228, -75.16511 40.02165, -75.16459 40.02136, -75.16447 40.02130, -75.16391 40.02099, -75.16...	2662	421189	421189	0	0	101	NaN	NaN	S	42101024400	CT	G5020	244	Census Tract 244	20770510183838	42	024400	None	70	-075.1638925	+40.0248284	-075.1638925	+40.0248284	1374	3152	7.191082e+05	3469.999759	Census Tract 244, Philadelphia County, Pennsylvania	3453.0	2779.0	42	101	024400	80.480741	42101024400	11.3
4	POLYGON ((-75.05465 40.04464, -75.05449 40.04456, -75.05443 40.04453, -75.05429 40.04445, -75.05426 40.04444, -75.05406 40.04433, -75.05311 40.04376, -75.05223 40.04327, -75.05215 40.04321, -75.05...	2664	842716	828420	0	0	101	NaN	NaN	S	42101033200	CT	G5020	332	Census Tract 332	20770510237205	42	033200	None	70	-075.0449684	+40.0439733	-075.0449684	+40.0439733	1046	3292	1.439592e+06	5498.109229	Census Tract 332, Philadelphia County, Pennsylvania	2951.0	94.0	42	101	033200	3.185361	42101033200	0.0

	NAME	B03002_001E	B03002_004E	state	county	tract
0	Census Tract 27.01, Philadelphia County, Pennsylvania	4098	368	42	101	002701
1	Census Tract 27.02, Philadelphia County, Pennsylvania	4300	124	42	101	002702
2	Census Tract 28.01, Philadelphia County, Pennsylvania	4452	294	42	101	002801
3	Census Tract 28.02, Philadelphia County, Pennsylvania	5772	221	42	101	002802
4	Census Tract 29, Philadelphia County, Pennsylvania	3762	50	42	101	002900

Week 5AGetting Data Part 1: Working with APIs

Part 1: Introduction to APIs¶

Example #1: Automated data feeds¶

USGS real-time earthquake feeds¶

Example #2: GeoServices¶

Philadelphia ZIP codes¶

Small utility package to make querying GeoServices easier: esri2gpd¶

Another useful example: census tracts¶

Example #3: Downloading Philly data from CARTO¶

SQL Databases¶

General Query Syntax¶

Let's try it out in Python¶

At-Home Exercise: Visualizing shootings data¶

Step 1: Prep the data¶

Step 2: Plot the points¶

Step 3: Make a (more useful) hex bin map¶

Count the total number of rows in a table¶

Select all columns, limiting the total number returned¶

Select by specific column values¶

The easier way: carto2gpd¶

At-Home Exercise: Explore trends by month and day of week¶

Step 1: Convert the date column to DateTime objects¶

Step 2: Calculate number of shootings by month and day of week¶

Step 3: Make a heatmap using hvplot¶

Trends: more shootings on the weekends and in the summer months¶

Part 2: The Census API¶

US Census data is foundational¶

Getting census data (the old way)American Factfinder

Getting census data (the new way)census.data.gov

Getting census data: other options¶

Data USA¶

Census Reporter:¶

NHGIS¶

Example: poverty data¶

Via the API¶

How to find the right variable names?¶

A detailed Census API guide¶

Accessing the API is easier from Python¶

Example: the racial "dot" map¶

The "explorer" module¶

Step 1: Identify what dataset we want¶

Step 2: Initialize the API connection¶

Step 3: Find the variables we want to load¶

Step 4: Identify the geographies to use¶

Step 5: Build the query (finally)¶

Step 6: We need the block group geometries too!¶

Step 7: Merge the demographic data with geometries¶

We're done! Plot it to make sure it makes sense¶

Now let's prep the data for the dot map¶

Define a function to create random points¶

Calculate the dot map¶

Now let's plot it¶

At home exercise: lead poisoning in Philadelphia¶

Step 1. Download the demographic data for Philadelphia¶

Step 2. Download and merge in the census tract geometries¶

Use esri2gpd to load the data¶

Step 3. Calculate the black percentage¶

Step 4. Query CARTO to get the childhood lead levels by census tract¶

Step 5. Remove census tracts with missing lead measurements¶

Step 6. Merge the demographic and lead level data frames¶

Step 7. Trim to the columns we need¶

Step 8. Plot the results¶

Challenge: Step 9. Use seaborn to plot a 2d density map¶

Week 5A
Getting Data Part 1: Working with APIs

Small utility package to make querying GeoServices easier: `esri2gpd`¶

The easier way: `carto2gpd`¶

Step 1: Convert the date column to `DateTime` objects¶

Step 3: Make a heatmap using `hvplot`¶

Getting census data (the old way)
American Factfinder

Getting census data (the new way)
census.data.gov